import Core.MongoDB as MongoDB
import Core.Gadget as Gadget
import datetime
#from sklearn.ensemble import RandomForestClassifier
from sklearn import tree
import pandas as pd
import numpy as np
from sklearn.decomposition import PCA
from sklearn import preprocessing
import copy
import os

datetime3 = datetime.datetime(2018, 10, 1)
datetime3 = Gadget.ToUTCDateTime(datetime3)
datetime11 = datetime.datetime(2018, 8, 31)
datetime11 = Gadget.ToUTCDateTime(datetime11)
datetime22 = datetime.datetime(2018, 9, 1)
datetime22 = Gadget.ToUTCDateTime(datetime22)
filelist1 = ["CAPMAnnualAlpha","CAPMAnnualBeta","BookToMarket","Cap","CAPMIdioValotility","EBITTEVTTM","PriceCashFlowTTM","PriceEarningNetIncomeTTM","PriceEarningOperatingProfitTTM","PriceEarningTotalIncomeTTM","PriceFreeCashFlowTTM","PriceSalesTTM","SWIndustry","MonthlyReturn"]
filelist2 = ['CurrentRatioLYR', 'FCFToAssetTTM', 'GreenblattROCTTM', 'GrossProfitMarginTTM', 'GrossProfitOnAssetTTM', 'GrowthCashFlow1Yr', 'GrowthEarning1Yr', 'GrowthFreeCashFlow1Yr', 'GrowthRevenue1Yr', 'MonthlyExcessReturn', 'ROATTM', 'ROETTM', 'ReceivablesTurnoverTTM', 'VolatilityAnnully', 'VolatilityHighLow']
filelist = ["CAPMAnnualAlpha","CAPMAnnualBeta","BookToMarket","Cap","CAPMIdioValotility","EBITTEVTTM","PriceCashFlowTTM","PriceEarningNetIncomeTTM","PriceEarningOperatingProfitTTM","PriceEarningTotalIncomeTTM","PriceFreeCashFlowTTM","PriceSalesTTM","SWIndustry","MonthlyReturn", 'CurrentRatioLYR', 'FCFToAssetTTM', 'GreenblattROCTTM', 'GrossProfitMarginTTM', 'GrossProfitOnAssetTTM', 'GrowthCashFlow1Yr', 'GrowthEarning1Yr', 'GrowthFreeCashFlow1Yr', 'GrowthRevenue1Yr', 'MonthlyExcessReturn', 'ROATTM', 'ROETTM', 'ReceivablesTurnoverTTM', 'VolatilityAnnully', 'VolatilityHighLow']

# 1. 从数据库取因子，得到index为股票名，columns为日期的因子文件
def GetFactor(database, datetime1, datetime2, factor_list):
    for fac_name in factor_list:
        print (fac_name)
        factor_data = pd.pivot_table(pd.DataFrame(database.find("Factor", fac_name, datetime1, datetime2)), values = 'Value', index = 'Symbol', columns = 'StdDateTime')
        monthlytime = ['2009-10-31 07:00:00+00:00', '2009-11-30 07:00:00+00:00', '2009-12-31 07:00:00+00:00', '2010-01-31 07:00:00+00:00', '2010-02-28 07:00:00+00:00', '2010-03-31 07:00:00+00:00', '2010-04-30 07:00:00+00:00', '2010-05-31 07:00:00+00:00', '2010-06-30 07:00:00+00:00', '2010-07-31 07:00:00+00:00', '2010-08-31 07:00:00+00:00', '2010-09-30 07:00:00+00:00', '2010-10-31 07:00:00+00:00', '2010-11-30 07:00:00+00:00', '2010-12-31 07:00:00+00:00',
                       '2011-01-31 07:00:00+00:00', '2011-02-28 07:00:00+00:00', '2011-03-31 07:00:00+00:00', '2011-04-30 07:00:00+00:00', '2011-05-31 07:00:00+00:00', '2011-06-30 07:00:00+00:00', '2011-07-31 07:00:00+00:00', '2011-08-31 07:00:00+00:00', '2011-09-30 07:00:00+00:00', '2011-10-31 07:00:00+00:00', '2011-11-30 07:00:00+00:00', '2011-12-31 07:00:00+00:00',
                       '2012-01-31 07:00:00+00:00', '2012-02-29 07:00:00+00:00', '2012-03-31 07:00:00+00:00', '2012-04-30 07:00:00+00:00', '2012-05-31 07:00:00+00:00', '2012-06-30 07:00:00+00:00', '2012-07-31 07:00:00+00:00', '2012-08-31 07:00:00+00:00', '2012-09-30 07:00:00+00:00', '2012-10-31 07:00:00+00:00', '2012-11-30 07:00:00+00:00', '2012-12-31 07:00:00+00:00',
                       '2013-01-31 07:00:00+00:00', '2013-02-28 07:00:00+00:00', '2013-03-31 07:00:00+00:00', '2013-04-30 07:00:00+00:00', '2013-05-31 07:00:00+00:00', '2013-06-30 07:00:00+00:00', '2013-07-31 07:00:00+00:00', '2013-08-31 07:00:00+00:00', '2013-09-30 07:00:00+00:00', '2013-10-31 07:00:00+00:00', '2013-11-30 07:00:00+00:00', '2013-12-31 07:00:00+00:00',
                       '2014-01-31 07:00:00+00:00', '2014-02-28 07:00:00+00:00', '2014-03-31 07:00:00+00:00', '2014-04-30 07:00:00+00:00', '2014-05-31 07:00:00+00:00', '2014-06-30 07:00:00+00:00', '2014-07-31 07:00:00+00:00', '2014-08-31 07:00:00+00:00', '2014-09-30 07:00:00+00:00', '2014-10-31 07:00:00+00:00', '2014-11-30 07:00:00+00:00', '2014-12-31 07:00:00+00:00',
                       '2015-01-31 07:00:00+00:00', '2015-02-28 07:00:00+00:00', '2015-03-31 07:00:00+00:00', '2015-04-30 07:00:00+00:00', '2015-05-31 07:00:00+00:00', '2015-06-30 07:00:00+00:00', '2015-07-31 07:00:00+00:00', '2015-08-31 07:00:00+00:00', '2015-09-30 07:00:00+00:00', '2015-10-31 07:00:00+00:00', '2015-11-30 07:00:00+00:00', '2015-12-31 07:00:00+00:00',
                       '2016-01-31 07:00:00+00:00', '2016-02-29 07:00:00+00:00', '2016-03-31 07:00:00+00:00', '2016-04-30 07:00:00+00:00', '2016-05-31 07:00:00+00:00', '2016-06-30 07:00:00+00:00', '2016-07-31 07:00:00+00:00', '2016-08-31 07:00:00+00:00', '2016-09-30 07:00:00+00:00', '2016-10-31 07:00:00+00:00', '2016-11-30 07:00:00+00:00', '2016-12-31 07:00:00+00:00',
                       '2017-01-31 07:00:00+00:00', '2017-02-28 07:00:00+00:00', '2017-03-31 07:00:00+00:00', '2017-04-30 07:00:00+00:00', '2017-05-31 07:00:00+00:00', '2017-06-30 07:00:00+00:00', '2017-07-31 07:00:00+00:00', '2017-08-31 07:00:00+00:00', '2017-09-30 07:00:00+00:00', '2017-10-31 07:00:00+00:00', '2017-11-30 07:00:00+00:00', '2017-12-31 07:00:00+00:00',
                       '2018-01-31 07:00:00+00:00', '2018-02-28 07:00:00+00:00', '2018-03-31 07:00:00+00:00',
                       '2018-04-30 07:00:00+00:00', '2018-05-31 07:00:00+00:00', '2018-06-30 07:00:00+00:00',
                       '2018-07-31 07:00:00+00:00', '2018-08-31 07:00:00+00:00', '2018-09-30 07:00:00+00:00']

        seltime = ['2010-01-31 07:00:00+00:00', '2010-02-28 07:00:00+00:00', '2010-03-31 07:00:00+00:00', '2010-04-30 07:00:00+00:00', '2010-05-31 07:00:00+00:00', '2010-06-30 07:00:00+00:00', '2010-07-31 07:00:00+00:00', '2010-08-31 07:00:00+00:00', '2010-09-30 07:00:00+00:00', '2010-10-31 07:00:00+00:00', '2010-11-30 07:00:00+00:00', '2010-12-31 07:00:00+00:00',
                       '2011-01-31 07:00:00+00:00', '2011-02-28 07:00:00+00:00', '2011-03-31 07:00:00+00:00', '2011-04-30 07:00:00+00:00', '2011-05-31 07:00:00+00:00', '2011-06-30 07:00:00+00:00', '2011-07-31 07:00:00+00:00', '2011-08-31 07:00:00+00:00', '2011-09-30 07:00:00+00:00', '2011-10-31 07:00:00+00:00', '2011-11-30 07:00:00+00:00', '2011-12-31 07:00:00+00:00',
                       '2012-01-31 07:00:00+00:00', '2012-02-29 07:00:00+00:00', '2012-03-31 07:00:00+00:00', '2012-04-30 07:00:00+00:00', '2012-05-31 07:00:00+00:00', '2012-06-30 07:00:00+00:00', '2012-07-31 07:00:00+00:00', '2012-08-31 07:00:00+00:00', '2012-09-30 07:00:00+00:00', '2012-10-31 07:00:00+00:00', '2012-11-30 07:00:00+00:00', '2012-12-31 07:00:00+00:00',
                       '2013-01-31 07:00:00+00:00', '2013-02-28 07:00:00+00:00', '2013-03-31 07:00:00+00:00', '2013-04-30 07:00:00+00:00', '2013-05-31 07:00:00+00:00', '2013-06-30 07:00:00+00:00', '2013-07-31 07:00:00+00:00', '2013-08-31 07:00:00+00:00', '2013-09-30 07:00:00+00:00', '2013-10-31 07:00:00+00:00', '2013-11-30 07:00:00+00:00', '2013-12-31 07:00:00+00:00',
                       '2014-01-31 07:00:00+00:00', '2014-02-28 07:00:00+00:00', '2014-03-31 07:00:00+00:00', '2014-04-30 07:00:00+00:00', '2014-05-31 07:00:00+00:00', '2014-06-30 07:00:00+00:00', '2014-07-31 07:00:00+00:00', '2014-08-31 07:00:00+00:00', '2014-09-30 07:00:00+00:00', '2014-10-31 07:00:00+00:00', '2014-11-30 07:00:00+00:00', '2014-12-31 07:00:00+00:00',
                       '2015-01-31 07:00:00+00:00', '2015-02-28 07:00:00+00:00', '2015-03-31 07:00:00+00:00', '2015-04-30 07:00:00+00:00', '2015-05-31 07:00:00+00:00', '2015-06-30 07:00:00+00:00', '2015-07-31 07:00:00+00:00', '2015-08-31 07:00:00+00:00', '2015-09-30 07:00:00+00:00', '2015-10-31 07:00:00+00:00', '2015-11-30 07:00:00+00:00', '2015-12-31 07:00:00+00:00',
                       '2016-01-31 07:00:00+00:00', '2016-02-29 07:00:00+00:00', '2016-03-31 07:00:00+00:00', '2016-04-30 07:00:00+00:00', '2016-05-31 07:00:00+00:00', '2016-06-30 07:00:00+00:00', '2016-07-31 07:00:00+00:00', '2016-08-31 07:00:00+00:00', '2016-09-30 07:00:00+00:00', '2016-10-31 07:00:00+00:00', '2016-11-30 07:00:00+00:00', '2016-12-31 07:00:00+00:00',
                       '2017-01-31 07:00:00+00:00', '2017-02-28 07:00:00+00:00', '2017-03-31 07:00:00+00:00', '2017-04-30 07:00:00+00:00', '2017-05-31 07:00:00+00:00', '2017-06-30 07:00:00+00:00', '2017-07-31 07:00:00+00:00', '2017-08-31 07:00:00+00:00', '2017-09-30 07:00:00+00:00', '2017-10-31 07:00:00+00:00', '2017-11-30 07:00:00+00:00', '2017-12-31 07:00:00+00:00',
                       '2018-01-31 07:00:00+00:00', '2018-02-28 07:00:00+00:00', '2018-03-31 07:00:00+00:00',
                       '2018-04-30 07:00:00+00:00', '2018-05-31 07:00:00+00:00', '2018-06-30 07:00:00+00:00',
                       '2018-07-31 07:00:00+00:00', '2018-08-31 07:00:00+00:00', '2018-09-30 07:00:00+00:00']
        monthlytime_copy = copy.deepcopy(monthlytime)
        fac_column = list(map(lambda x: str(x), factor_data.columns))
        ## 以下思路：将因子本身的日期与月末日期合并排序，将因子数据填充在表格并按向前填充再取出月末日期，这样保证了每个因子的日期一致，便于后续拼接
        for time in monthlytime_copy:
            if time in factor_data.columns:    #如果取出的因子日期在上述月末的时间list里，则删除此日期，避免同一个日期重复出现
                monthlytime.remove(time)
        #factor_currentratio.columns = pd.to_datetime(factor_currentratio.columns)
        #monthlytime = pd.to_datetime(monthlytime)
        column = fac_column
        for t in monthlytime:
            column.append(t)
        newcolumns = sorted(column)   #将月末日期与因子数据日期拼起来并排序
        #new_column = list(map(lambda x: pd.to_datetime(x), newcolumns))
        data = pd.DataFrame(index = factor_data.index, columns = newcolumns)
        factor_data.columns = map(lambda x: str(x), factor_data.columns)
        data.ix[:, fac_column] = factor_data
        data = data.fillna(method = 'ffill', axis = 1)    #将因子数据填充并按向前填充nan
        newdata = data[seltime]

        newdata.to_csv('D:/new_factor/factor_' + fac_name + '.csv', encoding = 'GBK')


# 2. 从库里取因子拼接，直接得到index为股票，columns为因子的文件（手动改日期，不推荐，可用GetFactor得到因子再处理成股票-因子的格式）
def GetMultiFactors(database, factor_list):
    datetime1 = datetime.datetime(2018, 8, 30)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(2018, 9, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    datetime3 = datetime.datetime(2018, 10, 1)
    datetime3 = Gadget.ToUTCDateTime(datetime3)
    datetime11 = datetime.datetime(2018, 8, 31)
    datetime11 = Gadget.ToUTCDateTime(datetime11)
    datetime22 = datetime.datetime(2018, 9, 1)
    datetime22 = Gadget.ToUTCDateTime(datetime22)

    # 如果是return类因子，则取t+1月，行业数据按季度更新，按datetime11和datetime22取
    strategy_data = pd.DataFrame()
    for file in factor_list:
        print(file)
        if 'Return' in file:
            fac_data = pd.DataFrame(database.find("Factor", file, datetime2, datetime3))[['Symbol', 'Value']].set_index('Symbol')
            fac_data = fac_data[fac_data['Value'] != 0]
            fac_data.columns = [file]
        elif file == 'SWIndustry':
            fac_data = pd.DataFrame(database.find("Factor", file, datetime11, datetime22))[['Symbol', 'Value']].set_index('Symbol')
            #print (fac_data.head())
            fac_data.columns = [file]
        elif file == 'Cap':
            fac_data = pd.DataFrame(data = np.nan, index = strategy_data.index, columns = [file])
        else:
            fac_data = pd.DataFrame(database.find("Factor", file, datetime1, datetime2))[['Symbol', 'Value']].set_index('Symbol')
            fac_data.columns = [file]
        strategy_data = pd.concat([strategy_data, fac_data], axis = 1)
        #print (strategy_data.columns)
    strategy_data.to_csv('D:/test/train_data_201808.csv', encoding = 'GBK')

# 3. 为index为股票，columns为因子的文件新增几个新因子（新因子来自股票-日期的本地因子数据）
def AddNewFactor(factor_list_new):
    # 将test文件夹里的表格与新增的因子文件按日期拼接
    sel_month = ['2017-05-31 07:00:00+00:00', '2017-06-30 07:00:00+00:00', '2017-07-31 07:00:00+00:00',
                 '2017-08-31 07:00:00+00:00', '2017-09-30 07:00:00+00:00', '2017-10-31 07:00:00+00:00',
                 '2017-11-30 07:00:00+00:00', '2017-12-31 07:00:00+00:00',
                 '2018-01-31 07:00:00+00:00', '2018-02-28 07:00:00+00:00', '2018-03-31 07:00:00+00:00',
                 '2018-04-30 07:00:00+00:00', '2018-05-31 07:00:00+00:00']
    for time in sel_month:
        year = time[:4]
        month = time[5:7]
        fac1 = pd.read_csv('D:/test/train_data_' + year + month + '.csv', encoding='GBK').set_index('Unnamed: 0')
        for file in factor_list_new:
            fac_data = pd.read_csv('D:/new_factor/factor_' + file + '.csv', encoding='GBK').set_index('Symbol')
            fac2 = fac_data[[time]]
            fac2.columns = [file]
            fac1 = pd.concat([fac1, fac2], axis = 1)
        fac1.to_csv('D:/strategy_data/strategy_data_' + year + month + '.csv', encoding = 'GBK')


# 4. 为index为股票，columns为因子的文件新增几个新因子（新因子取自数据库，因此要确定datetime1和datetime2）
def AddNewFactor_FromDatabase(database, datetime1, datetime2):
    #database = MongoDB.MongoDB("10.13.38.31", "27017")
    factor_test = pd.pivot_table(pd.DataFrame(
        database.find("Factor", 'MonthlyExcessReturn', datetime1, datetime2, query={"Symbol": '000001.SZ'})),
                                 values='Value', index='Symbol', columns='StdDateTime')
    factor_test.to_csv('D:/fa.csv', encoding='GBK')  # 取一只股票的数据是为了得到因子的时间戳

    a = pd.read_csv('D:/fa.csv', encoding = 'GBK').set_index('Symbol')  #先存再取是为了此时时间的格式比较好处理
    timelist = a.columns.tolist()

    for time in timelist:
        print (time)
        year = int(time[0:4])
        month = int(time[5:7])
        day = int(time[8:10])
    #print (year, month, day)
        if month == 12:
            nextyear = year + 1
            nextmonth = 1
        if month != 12:
            nextyear = year
            nextmonth = month + 1
        try:
            datetime1 = datetime.datetime(year, month, day) + datetime.timedelta(1)
            datetime2 = datetime.datetime(nextyear, nextmonth, 1) + datetime.timedelta(1)
            datetime1 = Gadget.ToUTCDateTime(datetime1)
            datetime2 = Gadget.ToUTCDateTime(datetime2)
            fac_volannully = pd.pivot_table(pd.DataFrame(database.find("Factor", 'VolatilityAnnully', datetime1, datetime2)), values = 'Value', index = 'Symbol', columns = 'StdDateTime')
            fac_volannully.columns = ['VolatilityAnnully']
            fac_volhighlow = pd.pivot_table(pd.DataFrame(database.find("Factor", 'VolatilityHighLow', datetime1, datetime2)), values = 'Value', index = 'Symbol', columns = 'StdDateTime')
            fac_volhighlow.columns = ['VolatilityHighLow']
        except:
            datetime1 = datetime.datetime(year, month, day)
            datetime2 = datetime.datetime(nextyear, nextmonth, 1) + datetime.timedelta(1)
            datetime1 = Gadget.ToUTCDateTime(datetime1)
            datetime2 = Gadget.ToUTCDateTime(datetime2)
            fac_volannully = pd.pivot_table(pd.DataFrame(database.find("Factor", 'VolatilityAnnully', datetime1, datetime2)), values = 'Value', index = 'Symbol', columns = 'StdDateTime')
            fac_volannully.columns = ['VolatilityAnnully']
            fac_volhighlow = pd.pivot_table(pd.DataFrame(database.find("Factor", 'VolatilityHighLow', datetime1, datetime2)), values = 'Value', index = 'Symbol', columns = 'StdDateTime')
            fac_volhighlow.columns = ['VolatilityHighLow']

        data = pd.read_csv('D:/strategy_data/strategy_data_' + str(year) + ('0'+str(month))[-2:] + '.csv', encoding = 'GBK').set_index('Unnamed: 0')
        data_table = pd.concat([data, fac_volannully, fac_volhighlow], axis = 1)
        data_table.to_csv('D:/StrategyData/strategy_data_' + str(year) + ('0'+str(month))[-2:] + '.csv', encoding = 'GBK')

# 5. 将股票-日期数据格式转为股票-因子数据格式
def Transfer():
    path = 'D:/new_factor'
    filename = os.listdir(path)
    data_global = pd.read_csv(path + '/' + filename[0], encoding='GBK').set_index('Symbol')
    timelist = data_global.columns.tolist()
    for time in timelist:
        data_df = pd.DataFrame()
        for file in filename:
            data = pd.read_csv(path + '/' + file, encoding='GBK').set_index('Symbol')
            data_use = data[[time]]
            data_use.columns = file.split('_')[1]
            data_df = pd.concat([data_df, data_use], axis=1)
        data_df.to_csv("D:/test/train_data_" + time[0:4] + time[5:7] + '.csv', encoding='GBK')













# 简单的按条件筛选股票
'''
def GetMonthlyStock(Year, Month):
    database = MongoDB.MongoDB("10.13.38.25", "27017")
    datetime1 = datetime.datetime(Year, Month, 1)
    datetime1 = Gadget.ToUTCDateTime(datetime1)
    datetime2 = datetime.datetime(Year, Month+1, 1)
    datetime2 = Gadget.ToUTCDateTime(datetime2)
    #sz_index = database.find("Instruments", "Stock")#, datetime1,datetime2)
    pb_index = pd.DataFrame(database.find("Factor", "PriceBookLF", datetime1,datetime2))
    pb_table = pd.pivot_table(pb_index, values = 'Value', index = 'Symbol', columns = 'StdDateTime')
    pb_select = pb_table[pb_table[pb_table.columns[0]] < 0.8]
    pb_select.columns = ['pb']

    pe_index = pd.DataFrame(database.find("Factor", "PriceEarningOperatingProfitTTM", datetime1,datetime2))
    pe_table = pd.pivot_table(pe_index, values = 'Value', index = 'Symbol', columns = 'StdDateTime')
    pe_select = pe_table[pe_table[pe_table.columns[0]] < 12]
    pe_select.columns = ['pe']

    select = pd.concat([pb_select, pe_select], axis = 1).dropna(how = 'any')

    return select.index

stocklist = GetMonthlyStock(Year = 2017, Month = 5)
print (stocklist)
'''
